package csc.fresher_group2.dao;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.Reader;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Properties;

import javax.servlet.ServletContext;

import com.ibatis.common.jdbc.ScriptRunner;

import csc.fresher_group2.model.Account;
import csc.fresher_group2.model.UserLog;

/*
 * Manage database connection
 */
public class DBFactory {
	private static Connection connection;
	private static final String PROTOCOL = "jdbc:derby:";
	private static final String DB_NAME = "MyDB";
	private static final String USER = "";
	private static final String PASSWORD = "";
	private static final String driver = "org.apache.derby.jdbc.EmbeddedDriver";
	
	public static Statement getStatement() throws SQLException{
		if(connection==null){
			openConnection();
		}
		
		return connection.createStatement();
	}
	
	private static Connection openConnection(){
		Properties userInfo = new Properties();
		userInfo.setProperty("user", USER);
		userInfo.setProperty("password", PASSWORD);
		
//		String pathScript = "C:\\Users\\Yen Nguyen\\Desktop\\JavaFresher\\Java Servlet and JSP\\FinalProject\\servletjsp-final-team2\\teamdb.sql";
//		String pathScript = "/WEB-INF/teamdb.sql";
		
		String url = PROTOCOL + DB_NAME + ";create=true";
		try {
			Class.forName(driver);
			connection = DriverManager.getConnection(url, userInfo);
//			DatabaseMetaData dbmd = connection.getMetaData();
//			ResultSet rs = dbmd.getTables(null, "APP", "ACCOUNT" , null);
//			if(!rs.next()){
//				initDB(pathScript, userInfo, connection);
//			}
			return connection;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	
	public static boolean release(){
		if(connection!=null){
			try {
				connection.close();
				connection = null;
				return true;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				return false;
			}
		}
		return false;
	}
	
	public static void initDB(String path){
		
		try{
			if(connection==null){
				openConnection();
			}
			DatabaseMetaData dbmd = connection.getMetaData();
			ResultSet rs = dbmd.getTables(null, "APP", "ACCOUNT" , null);
			if(!rs.next()){
				ScriptRunner sr = new ScriptRunner(connection, false, false);
				Reader reader = new BufferedReader(new FileReader(path));
				sr.runScript(reader);
			}
		}catch(SQLException e){
			System.err.println(e.getMessage());
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	
}
	
